﻿using Dapper_Core.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Dapper_Core.Common
{
    public class MySqlUtility
    {
        private readonly string connectionString = "server=127.0.0.1;port=3306;database=cm01;user=root;password=Hello123";
        private readonly string providerNameName = "MySql.Data.MySqlClient";

        public MySqlUtility()
        {

        }

        public MySqlUtility(IMySqlConnector connector)
        {
            this.connectionString = $"server={connector.Server};port={connector.Port};database={connector.Database};user={connector.User};password={connector.Password}";
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new MySqlParameter[0];
                    foreach (MySqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new MySqlParameter[0];
                    foreach (MySqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteDataTable

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new MySqlParameter[0];
                    foreach (MySqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null);
        }

        /// <summary>
        /// 出现异常时直接抛出
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数化查询所需参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    parameters = parameters ?? new MySqlParameter[0];
                    foreach (MySqlParameter item in parameters)
                    {
                        cmd.Parameters.Add(item);
                    }
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        #endregion
    }
}
